SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MiniCommerce_Store_FetchRandomProducts]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[MiniCommerce_Store_FetchRandomProducts] 

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
  SELECT TOP 6 v.*
  FROM vw_MiniCommerce_NotInActiveAndLocked_Products v 
  ORDER BY NEWID()
END

' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MiniCommerce_Store_FetchProductCrossSells]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


CREATE PROCEDURE [dbo].[MiniCommerce_Store_FetchProductCrossSells] 
  @ProductId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT * 
    FROM vw_MiniCommerce_NotInActiveAndLocked_Products 
    WHERE ProductId IN 
    (SELECT CrossProductId 
     FROM MiniCommerce_Store_CrossSell 
     WHERE ProductId = @ProductId) 
END


' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MiniCommerce_Store_FetchAssociatedAttributesByProductId]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[MiniCommerce_Store_FetchAssociatedAttributesByProductId]
	@ProductId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT 
		 attribute.AttributeId
		,attribute.AttributeTypeId
		,attribute.[Name]
		,attribute.Label
		,map.SortOrder
		,map.IsRequired
	FROM 
	MiniCommerce_Store_Attribute attribute
	INNER JOIN 
	MiniCommerce_Store_Product_Attribute_Map map
	ON 
	attribute.AttributeId = map.AttributeId
	WHERE map.ProductId = @ProductId
	ORDER BY map.SortOrder ASC
END

' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MiniCommerce_Store_FetchAvailableAttributesByProductId]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


CREATE PROCEDURE [dbo].[MiniCommerce_Store_FetchAvailableAttributesByProductId]
	@ProductId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT AttributeId, AttributeTypeId, [Name], Label
	FROM MiniCommerce_Store_Attribute
	WHERE AttributeId NOT IN 
	(SELECT AttributeId FROM MiniCommerce_Store_Product_Attribute_Map WHERE ProductId = @ProductId)
	ORDER BY [Name]
END


' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MiniCommerce_Store_FetchAssociatedCategoriesByProductId]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[MiniCommerce_Store_FetchAssociatedCategoriesByProductId]
	@ProductId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT 
		dbo.MiniCommerce_Store_Category.CategoryId,
		dbo.MiniCommerce_Store_Category.CategoryGuid,
		dbo.MiniCommerce_Store_Category.ParentId,
		dbo.MiniCommerce_Store_Category.[Name],
		dbo.MiniCommerce_Store_Category.ImageFile,
		dbo.MiniCommerce_Store_Category.Description,
		dbo.MiniCommerce_Store_Category.SortOrder,
		dbo.MiniCommerce_Store_Category.CreatedOn, 
		dbo.MiniCommerce_Store_Category.CreatedBy, 
		dbo.MiniCommerce_Store_Category.ModifiedOn, 
		dbo.MiniCommerce_Store_Category.ModifiedBy 
	FROM
		dbo.MiniCommerce_Store_Product_Category_Map 
		INNER JOIN dbo.MiniCommerce_Store_Category 
		ON dbo.MiniCommerce_Store_Product_Category_Map.categoryID = dbo.MiniCommerce_Store_Category.categoryID
	WHERE MiniCommerce_Store_Product_Category_Map.productID = @ProductId

END
' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MiniCommerce_Core_DeleteAllLogs]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[MiniCommerce_Core_DeleteAllLogs] 

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DELETE FROM MiniCommerce_Core_Log
END
' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MiniCommerce_Core_Insert_Log]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[MiniCommerce_Core_Insert_Log]
	-- Add the parameters for the stored procedure here
	@LogDate datetime,
	@Thread nvarchar(255),
	@LogLevel nvarchar(50),
	@Logger nvarchar(255),
	@LogMessage nvarchar(max),
	@LogInfo nvarchar(max),
	@Application nvarchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO MiniCommerce_Core_Log
		(
			 LogDate
			,Thread
			,LogLevel
			,Logger
			,LogMessage
			,LogInfo
			,[Application]
		)
	VALUES
		(
			 @LogDate
			,@Thread
			,@LogLevel
			,@Logger
			,@LogMessage
			,@LogInfo
			,@Application
		)
	
END
' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MiniCommerce_Core_FetchAllLogs]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[MiniCommerce_Core_FetchAllLogs] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * FROM MiniCommerce_Core_Log ORDER BY LogDate DESC
END
' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MiniCommerce_Store_FetchCategoryBrowsingLog]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[MiniCommerce_Store_FetchCategoryBrowsingLog] 

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

  -- Insert statements for procedure here
  SELECT DISTINCT(RelevantId), COUNT(RelevantId) AS Hits, c.[Name]
  FROM MiniCommerce_Store_Browsing_Log l INNER JOIN MiniCommerce_Store_Category c 
  ON l.RelevantId = c.CategoryId
  WHERE BrowsingBehaviorId = 1
  GROUP BY RelevantId, c.[Name]
  ORDER BY Hits DESC

END
' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MiniCommerce_Store_FetchFavoriteCategories]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[MiniCommerce_Store_FetchFavoriteCategories] 
	-- Add the parameters for the stored procedure here
  @UserName nvarchar(50),
  @BrowsingBehaviorId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT TOP 5 
        dbo.MiniCommerce_Store_Category.CategoryId
       ,dbo.MiniCommerce_Store_Category.[Name] 
	     ,COUNT(dbo.MiniCommerce_Store_Browsing_Log.BrowsingLogId) AS ViewCount
    FROM dbo.MiniCommerce_Store_Browsing_Log 
    INNER JOIN dbo.MiniCommerce_Store_Category 
    ON dbo.MiniCommerce_Store_Browsing_Log.RelevantId = dbo.MiniCommerce_Store_Category.CategoryId
    WHERE MiniCommerce_Store_Browsing_Log.UserName = @UserName 
          AND MiniCommerce_Store_Browsing_Log.BrowsingBehaviorId = @BrowsingBehaviorId
    GROUP BY 
            dbo.MiniCommerce_Store_Category.CategoryId
           ,dbo.MiniCommerce_Store_Category.[Name]
    ORDER BY ViewCount DESC
END
' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MiniCommerce_Store_FetchCategoryBreadCrumbs]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N' 
CREATE PROCEDURE [dbo].[MiniCommerce_Store_FetchCategoryBreadCrumbs] 
@CategoryId 
int 
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from 
-- interfering with SELECT statements. 
	SET NOCOUNT ON; 
	DECLARE @Result TABLE (CategoryId int, [Name] nvarchar(100 ), Description nvarchar(500), [Level] int) 
	DECLARE @Level int 
	SET @Level = 1 
	DECLARE @ParentId int 
	INSERT @Result 
	SELECT CategoryId, [Name], Description , @Level FROM MiniCommerce_Store_Category WHERE CategoryId = @CategoryId 
	SELECT @ParentId = ParentId FROM MiniCommerce_Store_Category WHERE CategoryId = @CategoryId 
		WHILE (@ParentId <> 0 ) 
		BEGIN 
		SET @CategoryId = @ParentId 
		INSERT @Result 
		SELECT CategoryId, [Name], Description , @Level + 1 FROM MiniCommerce_Store_Category WHERE CategoryId = @CategoryId 
		SELECT @ParentId = ParentId FROM MiniCommerce_Store_Category WHERE CategoryId = @CategoryId 
		SET @Level = @Level + 1 
		END 
	SELECT * FROM @Result ORDER BY [Level] DESC 
END' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MiniCommerce_Store_FetchProductBrowsingLog]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[MiniCommerce_Store_FetchProductBrowsingLog] 

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

  -- Insert statements for procedure here
  SELECT DISTINCT(RelevantId), COUNT(RelevantId) AS Hits, p.[Name]
  FROM MiniCommerce_Store_Browsing_Log l INNER JOIN MiniCommerce_Store_Product p 
  ON l.RelevantId = p.ProductId
  WHERE BrowsingBehaviorId = 2
  GROUP BY RelevantId, p.[Name]
  ORDER BY Hits DESC

END
' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MiniCommerce_Store_ProductSearch]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[MiniCommerce_Store_ProductSearch]
  @searchTerm nvarchar(100)
AS 
  BEGIN
    SET NOCOUNT ON

    SELECT *
    FROM MiniCommerce_Store_Product
    WHERE
    IsEnabled = 1 AND StatusId <> 99 AND ( 
    FREETEXT([Name], @searchTerm)
    OR
    FREETEXT([ShortDescription], @searchTerm)
    OR
    FREETEXT([BaseSku], @searchTerm))
    
  END

' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MiniCommerce_Store_FetchBrowsingLogSearchTerms]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[MiniCommerce_Store_FetchBrowsingLogSearchTerms] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
  SELECT DISTINCT TOP 5 (SearchTerms), COUNT(MiniCommerce_Store_Browsing_Log.BrowsingLogId) AS ViewCount 
  FROM MiniCommerce_Store_Browsing_Log 
  WHERE MiniCommerce_Store_Browsing_Log.BrowsingBehaviorId = 5
  GROUP BY SearchTerms
  ORDER BY ViewCount DESC

END

' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MiniCommerce_Store_FetchFavoriteProducts]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


CREATE PROCEDURE [dbo].[MiniCommerce_Store_FetchFavoriteProducts] 
  @UserName nvarchar(50),
  @BrowsingBehaviorId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

  SELECT TOP 5 
      v.ProductId
     ,v.[Name]
     ,COUNT(MiniCommerce_Store_Browsing_Log.BrowsingLogId) AS ViewCount 
  FROM MiniCommerce_Store_Browsing_Log 
  INNER JOIN vw_MiniCommerce_NotInActiveAndLocked_Products v 
  ON MiniCommerce_Store_Browsing_Log.RelevantId = v.ProductId
  WHERE MiniCommerce_Store_Browsing_Log.UserName = @UserName 
  AND MiniCommerce_Store_Browsing_Log.BrowsingBehaviorId = @BrowsingBehaviorId
  GROUP BY 
      v.ProductId
     ,v.[Name]
  ORDER BY ViewCount DESC
END


' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MiniCommerce_Store_FetchProductsByCategoryIdAndManufacturerId]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[MiniCommerce_Store_FetchProductsByCategoryIdAndManufacturerId] 
  @CategoryId int,
  @ManufacturerId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT v.*
	FROM vw_MiniCommerce_NotInActiveAndLocked_Products v
	WHERE v.ManufacturerId = @ManufacturerId 
  AND v.ProductId 
	IN (SELECT MiniCommerce_Store_Product_Category_Map.ProductId
		FROM MiniCommerce_Store_Product_Category_Map 
		WHERE MiniCommerce_Store_Product_Category_Map.CategoryId 
		IN (SELECT CategoryId FROM dbo.CategoryHierarchy(@CategoryId))) 
  ORDER BY SortOrder      
END

' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MiniCommerce_Store_FetchProductsByCategoryId]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


CREATE PROCEDURE [dbo].[MiniCommerce_Store_FetchProductsByCategoryId] 
	@CategoryId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT v.*
	FROM vw_MiniCommerce_NotInActiveAndLocked_Products v
	WHERE v.ProductId 
	IN (SELECT MiniCommerce_Store_Product_Category_Map.ProductId
		FROM MiniCommerce_Store_Product_Category_Map 
		WHERE MiniCommerce_Store_Product_Category_Map.CategoryId 
		IN (SELECT CategoryId FROM dbo.CategoryHierarchy(@CategoryId))) 
  ORDER BY SortOrder      
END


' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MiniCommerce_Store_FetchProductsByCategoryIdAndPriceRange]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[MiniCommerce_Store_FetchProductsByCategoryIdAndPriceRange] 
		@CategoryId int,
		@PriceStart money,
		@PriceEnd money
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT v.*
	FROM vw_MiniCommerce_NotInActiveAndLocked_Products v
	WHERE v.ProductId 
	IN (SELECT MiniCommerce_Store_Product_Category_Map.ProductId
		FROM MiniCommerce_Store_Product_Category_Map 
		WHERE MiniCommerce_Store_Product_Category_Map.CategoryId 
		IN (SELECT CategoryId FROM dbo.CategoryHierarchy(@CategoryId))) 
    AND (OurPrice BETWEEN @PriceStart AND @PriceEnd)
    ORDER BY SortOrder      
END

' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MiniCommerce_Store_FetchCategoryPriceRanges]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[MiniCommerce_Store_FetchCategoryPriceRanges] 
	@CategoryId int	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @MinPrice money, @MaxPrice money
	
	SELECT  @MinPrice = MIN(ourPrice)
		     ,@MaxPrice = MAX(ourPrice)
	FROM MiniCommerce_Store_Product 
	WHERE ProductId IN 
		(SELECT ProductId 
		 FROM MiniCommerce_Store_Product_Category_Map 
		 WHERE CategoryId IN (SELECT CategoryId FROM dbo.CategoryHierarchy(@CategoryId))
	)
	CREATE TABLE #prices(lowRange money, hiRange money)
	DECLARE @thisLow money, @thisHigh money
	SET @thisLow = @minPrice
	SET @thisHigh = 2 * @thisLow - 1
	IF @thisLow = 0
	BEGIN
		SET @thisHigh = 1
	END	

	DECLARE @loopCatch int
	
	--catch infinite loops
	SELECT @loopCatch = 1
	WHILE @thisLow <= @maxPrice AND @loopCatch < 100
		BEGIN
			INSERT INTO #prices(lowRange, hiRange) VALUES (@thisLow, @thisHigh)
			SET @thisLow = @thisHigh + 1
			SET @thisHigh = 2 * @thisLow - 1
			SELECT @loopCatch = @loopCatch + 1
		END
	SELECT * FROM #prices
	DROP TABLE #prices
END
' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MiniCommerce_Store_FetchCategoryManufacturers]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[MiniCommerce_Store_FetchCategoryManufacturers] 
	@CategoryId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT man.ManufacturerId, man.[Name]
	FROM MiniCommerce_Store_Manufacturer man
	INNER JOIN MiniCommerce_Store_Product prod ON man.ManufacturerId = prod.ManufacturerId
	INNER JOIN MiniCommerce_store_Product_Category_Map map ON prod.ProductId = map.ProductId
	WHERE (map.CategoryId IN (SELECT CategoryId FROM dbo.CategoryHierarchy(@CategoryId)))
	GROUP BY man.ManufacturerId, man.[Name] ORDER BY man.[Name]
END

' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MiniCommerce_Store_FetchAllProductsByCategoryId]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[MiniCommerce_Store_FetchAllProductsByCategoryId]
  @CategoryId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT MiniCommerce_Store_Product.*
	FROM MiniCommerce_Store_Product
	WHERE MiniCommerce_Store_Product.ProductId 
	IN (SELECT MiniCommerce_Store_Product_Category_Map.ProductId
		FROM MiniCommerce_Store_Product_Category_Map 
		WHERE MiniCommerce_Store_Product_Category_Map.CategoryId 
		IN (SELECT CategoryId FROM dbo.CategoryHierarchy(@CategoryId))) 
  ORDER BY SortOrder      
END
' 
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MiniCommerce_Store_FetchMostPopularProducts]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


CREATE PROCEDURE [dbo].[MiniCommerce_Store_FetchMostPopularProducts]
  @BrowsingBehviorId int 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT TOP 6 
        v.*
       ,COUNT(MiniCommerce_Store_Browsing_Log.BrowsingLogId) AS ViewCount 
    FROM MiniCommerce_Store_Browsing_Log 
    INNER JOIN vw_MiniCommerce_NotInActiveAndLocked_Products v 
    ON MiniCommerce_Store_Browsing_Log.RelevantId = v.ProductId
    WHERE MiniCommerce_Store_Browsing_Log.BrowsingBehaviorId = @BrowsingBehviorId
    GROUP BY 
        v.ProductId
       ,v.ManufacturerId
       ,v.StatusId
       ,v.ProductTypeId
       ,v.ShippingEstimateId
       ,v.BaseSku
       ,v.ProductGuid
       ,v.[Name]
       ,v.ShortDescription
       ,v.OurPrice
       ,v.RetailPrice
       ,v.Weight
       ,v.Length
       ,v.Height
       ,v.Width
       ,v.SortOrder
       ,v.RatingSum
       ,v.TotalRatingVotes
       ,v.IsEnabled
       ,v.AllowNegativeInventories
       ,v.CreatedBy
       ,v.CreatedOn
       ,v.ModifiedBy
       ,v.ModifiedOn
       ,v.IsDeleted
    ORDER BY ViewCount DESC
END
' 
END
GO

